// Package db provides database connection pooling functionality for the WePayKit application.
// It manages the connection to the PostgreSQL database and provides methods for executing queries.
package db

import (
    "database/sql"
    "fmt"
    "os"
    "sync"
    "time"

    _ "github.com/lib/pq"
    "github.com/we-pay-kit/we-pay-kit/internal/config"
    "github.com/we-pay-kit/we-pay-kit/internal/logger"
)

// DB represents a database connection pool.
type DB struct {
	*sql.DB
}

// Config holds the database configuration settings.
type Config struct {
	Host     string `json:"host"`
	Port     int    `json:"port"`
	User     string `json:"username"`
	Password string `json:"password"`
	DBName   string `json:"database"`
	SSLMode  string `json:"sslmode"`
}

// dbInstance holds the singleton database instance.
var (
	dbInstance *DB
	once       sync.Once
)

// GetDB returns the singleton database instance.
// It initializes the database connection pool on first call.
func GetDB() *DB {
	once.Do(func() {
		dbInstance = initDB()
	})
	return dbInstance
}

// initDB initializes the database connection pool.
// It reads the configuration from config.Get() and creates a connection pool.
func initDB() *DB {
	logger.Info("Initializing database connection")

    // Load configuration (env overrides supported)
    appCfg := config.Get()
    dbCfg := appCfg.DBInfo

    logger.Info(fmt.Sprintf("Database config: host=%s port=%d user=%s database=%s",
        dbCfg.Host, dbCfg.Port, dbCfg.User, dbCfg.DBName))

	// Create connection string
    connStr := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=%s",
        dbCfg.Host, dbCfg.Port, dbCfg.User,
        dbCfg.Password, dbCfg.DBName, dbCfg.SSLMode)

	// Open database connection pool
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		logger.Error(fmt.Sprintf("Failed to open database connection: %v", err))
		os.Exit(1)
	}

	// Configure connection pool settings
	db.SetMaxOpenConns(25)                 // Maximum number of open connections
	db.SetMaxIdleConns(25)                 // Maximum number of idle connections
	db.SetConnMaxLifetime(5 * time.Minute) // Maximum lifetime of a connection

	// Test the connection
	if err := db.Ping(); err != nil {
		logger.Error(fmt.Sprintf("Failed to ping database: %v", err))
		os.Exit(1)
	}

	logger.Info("Successfully connected to database")
	return &DB{db}
}

// Close closes the database connection pool.
func (db *DB) Close() error {
	return db.DB.Close()
}